﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using GeneralWare.DatabaseAccess;
using System.Net.Mail;
using System.Reflection;
using System.IO;
using System.Text.RegularExpressions;

namespace ncp.Business
{
    public class CommonBll
    {
        private const long shopCommonId = 103264587;
        //设置数据库连接串.
        public static void SetConnectionString(string ConnectionString, bool bEncrypt)
        {
            if (bEncrypt)
            {
                SqlHelper.ConnectionStringLocalTransaction = ConnectionString;
            }
            else
            {
                SqlHelper.ConnectionStringLocalTransaction = ConnectionString;
            }
        }

        //设置数据库连接串
        public static string GetConnectionString(string pDataBase)
        {
            return SqlHelper.ConnectionStringLocalTransaction.Replace("ncp", pDataBase);
        }

        //得到分页SQL语句
        /// <summary>
        /// 
        /// </summary>
        /// <param name="piPageIndex">当前页码</param>
        /// <param name="piPageSize">总页数</param>
        /// <param name="pcTableName">表名</param>
        /// <param name="pcFieldList">要显示的字段</param>
        /// <param name="pcFieldID">用于排序的字段(唯一)</param>
        /// <param name="pcOrderBy">排序方式</param>
        /// <param name="pcWhere">条件</param>
        /// <returns></returns>
        public static string GetPagingSQL(int piPageIndex, int piPageSize, string pcTableName, string pcFieldList, string pcFieldID, string pcOrderBy, string pcWhere)
        {
            string lcSQL = "";
            int liPriorNumber = 0;

            if (!string.IsNullOrEmpty(pcOrderBy.Trim()))
                pcOrderBy = " order by " + pcOrderBy + " ";

            if (piPageIndex > 0)
            {
                //liPriorNumber = (piPageIndex - 1) * piPageSize;
                liPriorNumber = piPageIndex * piPageSize;
                if (string.IsNullOrEmpty(pcWhere))
                {
                    lcSQL += string.Format("select top {0} {1}  from {2} where {3} Not in (", piPageSize.ToString(), pcFieldList, pcTableName, pcFieldID);
                    lcSQL += string.Format(" select top {0} {1} from {2} {3} ) ", liPriorNumber.ToString(), pcFieldID, pcTableName, pcOrderBy);
                }
                else
                {
                    lcSQL += string.Format("select top {0} {1}  from {2} where {3} and {4} Not in (", piPageSize.ToString(), pcFieldList, pcTableName, pcWhere, pcFieldID);
                    lcSQL += string.Format(" select top {0} {1} from {2} where {3} {4}) ", liPriorNumber.ToString(), pcFieldID, pcTableName, pcWhere, pcOrderBy);
                }
            }
            else
            {
                if (string.IsNullOrEmpty(pcWhere))
                {
                    lcSQL += string.Format("select top {0} {1}  from {2} ", piPageSize.ToString(), pcFieldList, pcTableName);
                }
                else
                {
                    lcSQL += string.Format("select top {0} {1}  from {2} where {3} ", piPageSize.ToString(), pcFieldList, pcTableName, pcWhere);
                }
            }
            if (!string.IsNullOrEmpty(pcOrderBy))
                lcSQL += pcOrderBy;
            return lcSQL;
        }

        public static DataTable GetdtList(string SQLString, params SqlParameter[] cmdParms)
        {
            using (SqlConnection connection = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return dt;
                }
            }
        }

        /// <summary>
        ///  给定连接的数据库用假设参数执行一个sql命令（不返回数据集）
        /// </summary>
        /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param>
        /// <param name="cmdText">存储过程名称或者sql命令语句</param>
        /// <param name="pDataBase">连接数据库</param>
        /// <param name="commandParameters">执行命令所用参数的集合</param>
        /// <returns>返回自增量ID</returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, string pDataBase, params SqlParameter[] commandParameters)
        {
            string connectionString = GetConnectionString(pDataBase);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader sdr = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                cmd.ExecuteNonQuery();
                cmd.CommandType=CommandType.Text;
                cmd.CommandText = "select @@identity as newID";
                sdr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                sdr.Read();
                int liResult = sdr["newID"] != DBNull.Value ? int.Parse(sdr["newID"].ToString()) : 0;
                return liResult;
            }
        }

        /// <summary>
        /// 准备执行一个命令
        /// </summary>
        /// <param name="cmd">sql命令</param>
        /// <param name="conn">Sql连接</param>
        /// <param name="trans">Sql事务</param>
        /// <param name="cmdType">命令类型例如 存储过程或者文本</param>
        /// <param name="cmdText">命令文本,例如：Select * from Products</param>
        /// <param name="cmdParms">执行命令的参数</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        public static DataTable GetdtList(CommandType cmdType,string SQLString, string pDataBase, params SqlParameter[] cmdParms)
        {
            string myConn = GetConnectionString(pDataBase);
            using (SqlConnection connection = new SqlConnection(myConn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;//120秒
                PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return dt;
                }
            }
        }

        public static DataTable GetdtList(string SQLString, string pDataBase, params SqlParameter[] cmdParms)
        {
            string myConn = GetConnectionString(pDataBase);
            using (SqlConnection connection = new SqlConnection(myConn))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;//120秒
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    DataTable dt = new DataTable();
                    try
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                    return dt;
                }
            }
        }

        public static List<T> GetetList<T>(string SQLString, string pDataBase, params SqlParameter[] cmdParms) where T : new()
        {
            string myConn = GetConnectionString(pDataBase);
            List<T> objs = new List<T>();
            PropertyInfo[] pros = typeof(T).GetProperties();
            using (SqlConnection connection = new SqlConnection(myConn))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString, cmdParms);
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    try
                    {
                        while (sdr.Read())
                        {
                            T obj = new T();
                            for (int i = 0; i < sdr.FieldCount; i++)
                            {
                                foreach (PropertyInfo pro in pros)
                                {
                                    if (sdr.GetName(i).ToUpper() == pro.Name.ToUpper())
                                    {
                                        if (sdr[pro.Name].GetType() != typeof(DBNull))
                                        {
                                            if (sdr[pro.Name] != DBNull.Value)
                                            {
                                                switch (pro.PropertyType.Name)
                                                {
                                                    case "Byte[]":
                                                        pro.SetValue(obj, (byte[])sdr[pro.Name], null);
                                                        break;
                                                    default:
                                                        try
                                                        {
                                                            pro.SetValue(obj, pro.PropertyType.GetMethod("Parse", new Type[] { typeof(string) }).Invoke(null, new object[] { sdr[pro.Name].ToString() }), null);
                                                        }
                                                        catch (Exception)
                                                        {
                                                            pro.SetValue(obj, Convert.ToString(sdr[pro.Name]).Trim(), null);
                                                        } break;
                                                }
                                            }
                                        }
                                    }

                                }

                            }
                            objs.Add(obj);
                        }
                        return objs;
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        cmd.Dispose();
                        connection.Close();
                    }
                }
            }
        }


        //ExecuteNonQuery 执行一条无返回值的 SQL 语句
        public static int ExecuteNonQuery(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            SqlCommand lSqlCommand;
            SqlConnection lSqlConnection = new SqlConnection(myConn);
            int liResult;

            try
            {
                if (lSqlConnection.State != ConnectionState.Open)
                    lSqlConnection.Open();
                lSqlCommand = new SqlCommand();
                lSqlCommand.CommandTimeout = 120;
                lSqlCommand.Connection = lSqlConnection;
                lSqlCommand.CommandText = pcSQL;
                lSqlCommand.CommandType = CommandType.Text;
                liResult = lSqlCommand.ExecuteNonQuery();
                lSqlConnection.Close();
                return liResult;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //ExecuteQuery 执行一条返回自增量的 SQL 语句
        public static int ExecuteQuery(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            SqlCommand lSqlCommand;
            SqlConnection lSqlConnection = new SqlConnection(myConn);
            SqlDataReader sdr = null;
            int liResult;

            try
            {
                if (lSqlConnection.State != ConnectionState.Open)
                    lSqlConnection.Open();
                lSqlCommand = new SqlCommand();
                lSqlCommand.Connection = lSqlConnection;
                lSqlCommand.CommandText = pcSQL;
                lSqlCommand.CommandType = CommandType.Text;
                lSqlCommand.ExecuteNonQuery();

                lSqlCommand.CommandText = "select @@identity as newID";
                sdr = lSqlCommand.ExecuteReader();
                sdr.Read();
                liResult = sdr["newID"] != DBNull.Value ? int.Parse(sdr["newID"].ToString()) : 0;
                return liResult;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 在事务中执行一条或多条 SQL 语句
        /// </summary>
        /// <param name="pcSQL">一条或多条 SQL 语句</param>
        /// <returns></returns>
        public static int ExecuteTransaction(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            int liResult;
            SqlTransaction lSqlTransaction = null;
            SqlCommand lSqlCommand;
            SqlConnection lSqlConnection = new SqlConnection(myConn);

            try
            {
                if (lSqlConnection.State != ConnectionState.Open)
                    lSqlConnection.Open();

                lSqlTransaction = lSqlConnection.BeginTransaction();

                lSqlCommand = new SqlCommand();
                lSqlCommand.Connection = lSqlConnection;
                lSqlCommand.CommandText = pcSQL;
                lSqlCommand.CommandType = CommandType.Text;
                lSqlCommand.Transaction = lSqlTransaction;

                liResult = lSqlCommand.ExecuteNonQuery();

                lSqlTransaction.Commit();
                lSqlConnection.Close();

                return liResult;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                lSqlTransaction.Rollback();
                lSqlConnection.Close();
                throw ex;
            }
            catch (Exception ex)
            {
                lSqlTransaction.Rollback();
                lSqlConnection.Close();
                throw ex;
            }
        }

        /// <summary>
        /// 在事务中执行一条或多条 SQL 语句, 并返回一个datatable
        /// </summary>
        /// <param name="pcSQL">一条或多条 SQL 语句</param>
        /// <returns></returns>
        public static DataTable ExecuteTransactionQuery(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            DataTable dtResult = new DataTable();
            SqlTransaction lSqlTransaction = null;
            SqlCommand lSqlCommand;
            SqlConnection lSqlConnection = new SqlConnection(myConn);

            try
            {
                if (lSqlConnection.State != ConnectionState.Open)
                    lSqlConnection.Open();

                lSqlTransaction = lSqlConnection.BeginTransaction();

                lSqlCommand = new SqlCommand();
                lSqlCommand.Connection = lSqlConnection;
                lSqlCommand.CommandText = pcSQL;
                lSqlCommand.CommandType = CommandType.Text;
                lSqlCommand.Transaction = lSqlTransaction;

                using (SqlDataAdapter da = new SqlDataAdapter(lSqlCommand))
                {
                    da.Fill(dtResult);
                    lSqlCommand.Parameters.Clear();
                }

                lSqlTransaction.Commit();
                lSqlConnection.Close();

                return dtResult;
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                lSqlTransaction.Rollback();
                lSqlConnection.Close();
                throw ex;
            }
            catch (Exception ex)
            {
                lSqlTransaction.Rollback();
                lSqlConnection.Close();
                throw ex;
            }
        }

        //读取一个整数值, 若是无值 DBNull 则返回 0
        public static int ReadInteger(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            object ob;
            try
            {
                ob = SqlHelper.ExecuteScalar(myConn, CommandType.Text, pcSQL, null);
                if (ob is DBNull)
                    return 0;
                return System.Convert.ToInt32(ob);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        public static int ReadInteger(string pcSQL, string pDataBase, params SqlParameter[] commandParameters)
        {
            string myConn = GetConnectionString(pDataBase);
            object ob;
            try
            {
                ob = SqlHelper.ExecuteScalar(myConn, CommandType.Text, pcSQL, commandParameters);
                if (ob is DBNull)
                    return 0;
                return System.Convert.ToInt32(ob);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        #region ReadString(string pcSQL),  读取一个字符串, 若为空 DBNull 返回 "";
        public static string ReadString(string pcSQL, string pDataBase)
        {
            string myConn = GetConnectionString(pDataBase);
            object ob;
            try
            {
                ob = SqlHelper.ExecuteScalar(myConn, CommandType.Text, pcSQL, null);
                if (ob is DBNull)
                    return "";
                return System.Convert.ToString(ob);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw ex;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        //得到Html模板内容
        public static string GetHtmlTemplate(string pHtmlPath)
        {
            StringBuilder htmltext = new StringBuilder();
            try
            {
                using (StreamReader sr = new StreamReader(pHtmlPath, Encoding.GetEncoding("UTF-8")))//打开模板
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        htmltext.Append(line);
                    }
                    sr.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return htmltext.ToString();
        }

        public static string SendEmail(string strContent, string strTo, string strCC)
        {
            try
            {
                if (strTo == "") return "";
                MailMessage myEmail = new MailMessage();
                MailAddress MailFrom = new MailAddress(CommonConst.FROM_EMAIL, CommonConst.FROM_DISPLAYNAME);
                myEmail.From = MailFrom;
                myEmail.To.Add(strTo);
                if (strCC != "") myEmail.CC.Add(strCC);
                myEmail.Body = strContent;
                myEmail.Subject = CommonConst.EMAIL_SUBJECT;
                myEmail.Priority = MailPriority.Normal;
                myEmail.IsBodyHtml = true;
                SmtpClient mysmtp = new SmtpClient(CommonConst.SMTP);
                mysmtp.Credentials = new System.Net.NetworkCredential(CommonConst.FROM_EMAIL, CommonConst.FROM_PWS);
                mysmtp.Send(myEmail);
                return "OK";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }




        /// <summary>
        /// 在0到max之间得到n个随机数(含0不含max)
        /// <param name="n"></param>
        /// <param name="max"></param>
        /// <returns></returns>
        public static int[] GetRandom(int n, int max)
        {
            List<int> list = new List<int>();
            Random rd = new Random();
            if (n >= max)
            {
                for (int i = 0; i < max; i++)
                {
                    list.Add(i);
                }
            }
            else
            {
                int item = rd.Next(0, max);
                for (int i = 0; i < n; i++)
                {
                    while (list.Contains(item))
                    {
                        item = rd.Next(1, max);
                    }
                    list.Add(item);
                }
            }
            return list.ToArray();
        }


        /// <summary>
        /// 加密(加密后的字符串可能会包含单引号，因此用于业务逻辑层构建sql时，需替换成两个单引号，UI层则不作处理,除非需要在UI层构建sql)
        /// </summary>
        /// <param name="txt"></param>
        /// <returns></returns>
        public static string SecurityPWD(string txt)
        {
            string result = "";
            int tmp;
            for (int i = 1; i <= txt.Length; i++)
            {
                tmp = Convert.ToInt32(Convert.ToChar(txt.Substring(i - 1, 1)));
                result += Convert.ToChar(tmp - 7).ToString();
            }
            return result;
        }

        /// <summary>
        /// 解密
        /// </summary>
        /// <param name="txt"></param>
        /// <returns></returns>
        public static string DesecurityPWD(string txt)
        {
            string result = "";
            int tmp;
            for (int i = 1; i <= txt.Length; i++)
            {
                tmp = Convert.ToInt32(Convert.ToChar(txt.Substring(i - 1, 1)));
                result += Convert.ToChar(tmp + 7).ToString();
            }
            return result;
        }


        public static T DictionaryToMap<T>(Dictionary<string, object> pDictionary) where T : new()
        {
            PropertyInfo[] pros = typeof(T).GetProperties();
            T obj = new T();
            foreach (PropertyInfo pro in pros)
            {
                if (pDictionary.ContainsKey(pro.Name.ToLower()))
                {
                    object pDictionaryName = pro.Name.ToLower();
                    object pDictionaryValue = pDictionary[pro.Name.ToLower()];

                    if (!string.IsNullOrEmpty(pDictionaryValue.ToString()))
                    {
                        switch (pro.PropertyType.Name)
                        {
                            case "Byte[]":
                                pro.SetValue(obj, (byte[])pDictionaryValue, null);
                                break;
                            default:
                                try
                                {
                                    pro.SetValue(obj, pro.PropertyType.GetMethod("Parse", new Type[] { typeof(string) }).Invoke(null, new object[] { pDictionaryValue.ToString() }), null);
                                }
                                catch (Exception)
                                {
                                    pro.SetValue(obj, Convert.ToString(pDictionaryValue).Trim(), null);
                                } break;
                        }
                    }

                }
            }
            return obj;
        }

        /// <summary>
        /// 另存Html文件
        /// </summary>
        public static void SetHtmlPage(string pHtmlPath, string pDescPath, string strFieldList, string strFormList)
        {
            StringBuilder htmltext = new StringBuilder();
            try
            {
                using (StreamReader sr = new StreamReader(pHtmlPath, System.Text.Encoding.GetEncoding("GB2312")))//打开模板
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        htmltext.Append(line);
                    }
                    sr.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            htmltext = htmltext.Replace("<!--FieldList-->", strFieldList);
            htmltext = htmltext.Replace("<!--FormList-->", strFormList);
            try
            {
                using (StreamWriter sw = new StreamWriter(pDescPath, false, System.Text.Encoding.GetEncoding("GB2312")))
                {
                    sw.WriteLine(htmltext);
                    sw.Flush();
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 保存文件，使用UTF-8编码，支持xls,doc,txt,html
        /// </summary>
        public static void SaveFileFromWeb(string pFilePath,string strConnent)
        {
            try
            {
                using (StreamWriter sw = new StreamWriter(pFilePath, false, Encoding.GetEncoding("UTF-8")))
                {
                    sw.WriteLine(strConnent);
                    sw.Flush();
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 写Html文件
        /// </summary>
        public static void WriteHtmlPage(string pHtmlPath, string pHtmlContent)
        {
            try
            {
                using (StreamWriter sw = new StreamWriter(pHtmlPath, false, Encoding.GetEncoding("UTF-8")))
                {
                    sw.WriteLine(pHtmlContent);
                    sw.Flush();
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 读HTML文件
        /// </summary>
        /// <param name="pHtmlPath"></param>
        /// <returns></returns>
        public static string ReadHtmlPage(string pHtmlPath)
        {
            StringBuilder htmltext = new StringBuilder();
            try
            {
                using (StreamReader sr = new StreamReader(pHtmlPath, Encoding.GetEncoding("UTF-8")))//打开模板
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        htmltext.Append(line);
                    }
                    sr.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return htmltext.ToString();
        }

        /// <summary>
        /// 是否为日期
        /// </summary>
        public static bool IsDate(string Expression)
        { 
            string regex = @"^((((1[6-9]|[2-9]\d)\d...{2})-(0?[13578]|1[02])-(0?[1-9]|[12]\d|3[01]))|(((1[6-9]|[2-9]\d)\d{2})-(0?[13456789]|1[012])-(0?[1-9]|[12]\d|30))|(((1[6-9]|[2-9]\d)\d{2})-0?2-(0?[1-9]|1\d|2[0-8]))|(((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))-0?2-29-))( (20|21|22|23|[0-1]?\d):[0-5]?\d:[0-5]?\d)?$"; 
            return Regex.IsMatch(Expression, regex); 
        }


        /// <summary>
        /// 数据库用户是否为只读
        /// </summary>
        public static int  IsReadOnlyDB(string ConnectionString, string p_DataBase)
        {
            string[] ConStr = ConnectionString.Split(';');
            string DbUid = "";
            for (int i = 0; i < ConStr.Length; i++)
            {
                if (ConStr[i].ToLower().Contains("user id"))
                {
                    DbUid = ConStr[i].Split('=')[1].Trim();
                }
            }
            string sqlStr = "create  table  #temp(username  varchar(200),groupname   varchar(200),loginname   varchar(200),defdbname   varchar(200),defschemaname   varchar(200),userid  int,[sid]  varbinary(200));insert  #temp  exec  sp_helpuser '" + DbUid + "';select  count(1)  from  #temp  where (groupname='db_owner') or (groupname='db_datawriter') or (groupname='db_ddladmin');drop  table  #temp;";
            int ii = CommonBll.ReadInteger(sqlStr, p_DataBase);
            return ii;
        }

        /// <summary>
        /// 数据库用户是否存在
        /// </summary>
        public static int IsExistDBUser(string ConnectionString, string p_DataBase)
        {
            string[] ConStr = ConnectionString.Split(';');
            string DbUid = "";
            for (int i = 0; i < ConStr.Length; i++)
            {
                if (ConStr[i].ToLower().Contains("user id"))
                {
                    DbUid = ConStr[i].Split('=')[1].Trim();
                }
            }
            string sqlStr = "create  table  #temp(username  varchar(200),groupname   varchar(200),loginname   varchar(200),defdbname   varchar(200),defschemaname   varchar(200),userid  int,[sid]  varbinary(200));insert  #temp  exec  sp_helpuser;select  count(1)  from  #temp  where username='" + DbUid + "';drop  table  #temp;";
            int ii = CommonBll.ReadInteger(sqlStr, p_DataBase);
            return ii;
        }

        /// <summary>
        /// 重置所有表的标识列种子
        /// </summary>
        public static int ReSeedAllTables(string ConnectionString, string p_DataBase)
        {
            string[] ConStr = ConnectionString.Split(';');
            string Data_Source = "";
            for (int i = 0; i < ConStr.Length; i++)
            {
                if (ConStr[i].ToLower().Contains("data source"))
                {
                    Data_Source = ConStr[i].Split('=')[1].Trim();
                }
            }
            int ii = 0;
            string sqlStr = "select  name  from   sysobjects   where   xtype='U'";//得到本库的所有用户表
            DataTable dt = CommonBll.GetdtList(sqlStr, p_DataBase);
            sqlStr = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sqlStr += "if   exists(select   *   from   syscolumns   where   id=object_id(N'" + dt.Rows[i][0].ToString() + "')   and   status=0x80) DBCC CHECKIDENT ('" + dt.Rows[i][0].ToString() + "', RESEED);";
            }
            sqlStr += "IF EXISTS (select 1 from SA_option  where option_name='Data_Source') " +
                "update SA_option set option_value='" + Data_Source + "' where option_name='Data_Source'" +
                " ELSE " +
                "insert into SA_option(option_value, option_name) values ('" + Data_Source + "','Data_Source');";

            ii = CommonBll.ExecuteTransaction(sqlStr, p_DataBase);
            return ii;
        }
        /// <summary>
        /// 该方法用来检测用户输入是否带有恶意
        /// </summary>
        /// <param name="text">用户输入的文字</param>
        /// <param name="maxlength">最大的长度</param>
        /// <returns>返回验证后的文字</returns>
        public static string InputText(string text, int maxlength)
        {
            text = text.ToLower().Trim();
            if (string.IsNullOrEmpty(text))
                return string.Empty;
            if (text.Length > maxlength)
                text = text.Substring(0, maxlength);

            text = Regex.Replace(text, "[\\s]{2,{", " ");
            text = Regex.Replace(text, "(<[b|B][r|R]/*>)+|(<[p|P](.|\\n)*?>)", "\n");	//<br>
            text = Regex.Replace(text, "(\\s*&[n|N][b|B][s|S][p|P];\\s*)+", " ");	//&nbsp;
            text = Regex.Replace(text, "<(.|\\n)*?>", string.Empty);	//any other tags
            text = Regex.Replace(text, "=", "");
            text = Regex.Replace(text, "%", "");
            text = Regex.Replace(text, "'", "");
            text = Regex.Replace(text, "select", "");
            text = Regex.Replace(text, "insert", "");
            text = Regex.Replace(text, "delete", "");
            text = Regex.Replace(text, "or", "");
            text = Regex.Replace(text, "exec", "");
            text = Regex.Replace(text, "--", "");
            text = Regex.Replace(text, "and", "");
            text = Regex.Replace(text, "where", "");
            text = Regex.Replace(text, "update", "");
            text = Regex.Replace(text, "script", "");
            text = Regex.Replace(text, "iframe", "");
            text = Regex.Replace(text, "master", "");
            text = Regex.Replace(text, "exec", "");
            text = Regex.Replace(text, "<", "");
            text = Regex.Replace(text, ">", "");
            text = Regex.Replace(text, "\r\n", "");

            return text;
        }

        //设置商铺的ID

        //判断是否为合法ID
        public static bool IsLegitimateShopId(string shop)
        {
            long shopId = 0;
            if (long.TryParse(shop, out shopId) && shopId > shopCommonId)
            {
                return true;
            }
            return false;
        }
        //返回shop的真实ID
        public static long GetTheRealId(string shop)
        {
            long shopId = 0;
            if (long.TryParse(shop, out shopId) && shopId > shopCommonId)
            {
                return shopId-shopCommonId;
            }
            return 0;
        }
        //生成shopID
        public static long BuildShopId(int shopId)
        {
            return shopId + shopCommonId;
        }
        #region 字符串截取函数
        public static string CutString(string str, int n,bool addStr)
        {
            //格式化字符串长度，超出部分显示省略号,区分汉字跟字母。汉字2个字节，字母数字一个字节
            string temp = string.Empty;
            if (System.Text.Encoding.Default.GetByteCount(str) <= n)//如果长度比需要的长度n小,返回原字符串
            {
                return str;
            }
            else
            {
                int t = 0;
                char[] q = str.ToCharArray();
                for (int i = 0; i < q.Length && t < n; i++)
                {
                    if ((int)q[i] >= 0x4E00 && (int)q[i] <= 0x9FA5)//是否汉字
                    {
                        temp += q[i];
                        t += 2;
                    }
                    else
                    {
                        temp += q[i];
                        t++;
                    }
                }
                if (addStr)
                {
                    temp += "...";
                }
                return temp;
            }
        }
        #endregion

        /// <summary>
        /// 去除字符串html
        /// </summary>
        /// <returns></returns>
        public static string NoHtml(string htmlstring)
        {
            //删除脚本
            htmlstring = Regex.Replace(htmlstring, @"<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase);
            //删除HTML
            htmlstring = Regex.Replace(htmlstring, @"<(.[^>]*)>", "", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"([/r/n])[/s]+", "", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"-->", "", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"<!--.*", "", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(quot|#34);", "\"", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(amp|#38);", "&", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(lt|#60);", "<", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(gt|#62);", ">", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(nbsp|#160);", " ", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(iexcl|#161);", "/xa1", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(cent|#162);", "/xa2", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(pound|#163);", "/xa3", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&(copy|#169);", "/xa9", RegexOptions.IgnoreCase);
            htmlstring = Regex.Replace(htmlstring, @"&#(/d+);", "", RegexOptions.IgnoreCase);
            htmlstring = htmlstring.Replace("<", "");
            htmlstring = htmlstring.Replace(">", "");
            htmlstring = htmlstring.Replace("/r/n", "");
            //                          Htmlstring=HttpContext.Current.Server.HtmlEncode(Htmlstring).Trim();
            return htmlstring;
        }

        //动态发布文件
        public static void ReplaceFileContent(string pSourceFile, string repStart, string repEnd, string repContent)
        {
            StringBuilder htmltext = new StringBuilder();
            try
            {
                using (StreamReader sr = new StreamReader(pSourceFile, Encoding.GetEncoding("UTF-8")))//打开模板
                {
                    String line;
                    while ((line = sr.ReadLine()) != null)
                    {
                        htmltext.Append(line);
                    }
                    sr.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            string str = htmltext.ToString();
            int start = str.LastIndexOf(repStart) + repStart.Length;
            int end = str.LastIndexOf(repEnd);
            string str1 = "";
            if (end - start > 0)
                str1 = str.Substring(start, end - start);
            if (str1 == "")
            {
                htmltext.Insert(start, repContent);
            }
            else
            {
                htmltext.Replace(str1, repContent);
            }
            try
            {
                using (StreamWriter sw = new StreamWriter(pSourceFile, false, Encoding.GetEncoding("UTF-8")))
                {
                    sw.WriteLine(htmltext);
                    sw.Flush();
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取客户端操作系统版本
        /// </summary>
        /// <param name="userAgent">用户浏览器userAgent</param>
        /// <returns>客户端操作系统版本</returns>
        public static string GetClientOS(string userAgent)
        {
            string osVersion = "Unknown OS";
            if (userAgent==null)
            {
                return osVersion;
            }
            if (userAgent.Contains("NT 6.1"))
            {
                osVersion = "Windows 7";
            }
            else if (userAgent.Contains("NT 6.0"))
            {
                osVersion = "Windows Vista/Server 2008";
            }
            else if (userAgent.Contains("NT 5.2"))
            {
                osVersion = "Windows Server 2003";
            }
            else if (userAgent.Contains("NT 5.1"))
            {
                osVersion = "Windows XP";
            }
            else if (userAgent.Contains("NT 5"))
            {
                osVersion = "Windows 2000";
            }
            else if (userAgent.Contains("NT 4"))
            {
                osVersion = "Windows NT4";
            }
            else if (userAgent.Contains("Me"))
            {
                osVersion = "Windows Me";
            }
            else if (userAgent.Contains("98"))
            {
                osVersion = "Windows 98";
            }
            else if (userAgent.Contains("95"))
            {
                osVersion = "Windows 95";
            }
            else if (userAgent.Contains("Mac"))
            {
                osVersion = "Mac";
            }
            else if (userAgent.Contains("Unix"))
            {
                osVersion = "UNIX";
            }
            else if (userAgent.Contains("Linux"))
            {
                osVersion = "Linux";
            }
            else if (userAgent.Contains("SunOS"))
            {
                osVersion = "SunOS";
            }
            return osVersion;
        }

        /// <summary> 
        ///  传入URL返回网页的html代码 
        /// </summary> 
        /// <param name="url"> URL</param>
        /// <returns> </returns> 
        public static string GetUrltoHtml(string url)
        {
            try
            {
                System.Net.WebRequest wReq = System.Net.WebRequest.Create(url);
                //   Get   the   response   instance. 
                System.Net.WebResponse wResp = wReq.GetResponse();
                //   Read   an   HTTP-specific   property 
                //if   (wResp.GetType()   ==HttpWebResponse) 
                //{ 
                //DateTime   updated     =((System.Net.HttpWebResponse)wResp).LastModified; 
                //} 
                //   Get   the   response   stream. 
                System.IO.Stream respStream = wResp.GetResponseStream();
                //   Dim   reader   As   StreamReader   =   New   StreamReader(respStream) 
                System.IO.StreamReader reader = new System.IO.StreamReader(respStream, System.Text.Encoding.UTF8);
                return reader.ReadToEnd();

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public static string GetProjectOption(string option_name, string pdataBase)
        {
            string str_sql = "select option_value from dbo.sa_option where option_name='" + option_name + "'";
            return ReadString(str_sql, pdataBase);
        }

        public static string SendEmail(string strContent, string strTo, string strCC, string pDatabase)
        {
            string smtp_server = GetProjectOption("smtp_server", pDatabase);
            string smtp_username = GetProjectOption("smtp_username", pDatabase);
            string smtp_pwd = GetProjectOption("smtp_pwd", pDatabase);
            if (smtp_server == "" || smtp_username == "" || smtp_pwd=="")
            {
                return "邮件服务不可用！";
            }
            try
            {
                if (strTo == "") return "";
                MailMessage myEmail = new MailMessage();
                MailAddress MailFrom = new MailAddress(smtp_username, CommonConst.FROM_DISPLAYNAME);
                myEmail.From = MailFrom;
                myEmail.To.Add(strTo);
                if (strCC != "") myEmail.CC.Add(strCC);
                myEmail.Body = strContent;
                myEmail.Subject = CommonConst.EMAIL_SUBJECT;
                myEmail.Priority = MailPriority.Normal;
                myEmail.IsBodyHtml = true;
                SmtpClient mysmtp = new SmtpClient(smtp_server);
                mysmtp.Credentials = new System.Net.NetworkCredential(smtp_username, smtp_pwd);
                mysmtp.Send(myEmail);
                return "OK";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}
